/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package tiebreak.DAL;

import com.microsoft.sqlserver.jdbc.SQLServerException;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import java.sql.SQLException;
import java.util.ArrayList;
import tiebreak.BE.MembershipType;
import tiebreak.BE.Settings;
import tiebreak.DAL.TBConnection;
import tiebreak.UI.AccountingPanel;

/**
 *
 * @author Låner
 */
public class DalcAccounting {
    static Connection m_connection;

    public static ArrayList<MembershipType> getMembershipTypes() throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("SELECT * FROM `membershiptype`"))
        {
            throw new SQLException("Could not retrieve data");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getType(result);
    }

    public static ArrayList<MembershipType> getPriceByType(String MemberType) throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("SELECT `name` FROM `membershiptype`"))
        {
            throw new SQLException("Could not retrieve data");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getType(result);
    }

    public static ArrayList<MembershipType> UpdatePrice(int Price) throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("UPDATE `price` FROM `membershiptype`"))
        {
            throw new SQLException("Could not retrieve data");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getType(result);
    }

     public static ArrayList<Settings> getFees() throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("SELECT * FROM `settings`"))
        {
            throw new SQLException("Could not retrieve data");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getFees(result);
    }

    public static void setEliteAndSchool(int elitePrice, int schoolPrice) throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        stm.execute("UPDATE `settings` SET `eliteprice`='"+elitePrice+"', `tennisschoolprice`='"+schoolPrice+"'");
    }

    public static void setMemberTypeFee(String memberType, int fee) throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        stm.execute("UPDATE `membershiptype` SET `price`='"+fee+"' WHERE `name`='"+memberType+"'");
    }

    public static ArrayList<MembershipType> getMembershipTypeFee(String type) throws SQLException
    {
        m_connection = (Connection) TBConnection.getConnection();
        Statement stm = (Statement) m_connection.createStatement();
        if(!stm.execute("SELECT * FROM `membershiptype` WHERE `name`='"+type+"'"))
        {
            throw new SQLException("Could not retrieve data");
        }
        ResultSet result = (ResultSet) stm.getResultSet();
        return getType(result);
    }

    private static ArrayList<MembershipType> getType(ResultSet result) throws SQLException
    {
        ArrayList<MembershipType> fee = new ArrayList<MembershipType>();
        while (result.next())
        {
            int id = result.getInt("id");
            String name = result.getString("name");
            int price = result.getInt("price");

            MembershipType c = new MembershipType(id, name, price);
            fee.add(c);
        }
        return fee;
    }

    private static ArrayList<Settings> getFees(ResultSet result) throws SQLException
    {
        ArrayList<Settings> fee = new ArrayList<Settings>();
        while (result.next())
        {
            int id = result.getInt("settingid");
            int elite = result.getInt("eliteprice");
            int school = result.getInt("tennisschoolprice");

            Settings c = new Settings(id, elite, school);
            fee.add(c);
        }
        return fee;
    }
}
